const connection = require('@/app/database')
const SQLSTM = require('@/utils/statement')
const snowflake = require('@/utils/snowflake')

class SystemService {
  // ================== 用户管理 ==================
  async selectUsers(query) {
    const statement = SQLSTM.select('sys_user', {
      attrs: ['id', 'account', 'nickName', 'sex', 'roleId', 'remark', 'createTime'],
      limit: {
        pageNum: query.pageNum,
        pageSize: query.pageSize,
      },
    })
    const [list] = await connection.execute(statement)
    const [[{ total }]] = await connection.execute(
      SQLSTM.select('sys_user', {
        attrs: [{ fun: 'count', attr: '*', as: 'total' }],
      })
    )
    return {
      list,
      total,
    }
  }
  async insertUser(body) {
    const [[resule]] = await connection.execute(
      SQLSTM.select('sys_role', {
        attrs: ['roleName'],
        where: {
          id: body.roleId,
        },
      })
    )
    const statement = SQLSTM.insert('sys_user', { ...body, roleName: resule.roleName })
    await connection.execute(statement)
  }
  async updateUser(body) {
    const statement = SQLSTM.update('sys_user', {
      attrs: {
        account: body.account,
        nickName: body.nickName,
        sex: body.sex,
        roleId: body.roleId,
        remark: body.remark,
      },
      where: {
        id: body.id,
      },
    })
    await connection.execute(statement)
  }
  async deleteUser(id) {
    const statement = SQLSTM.delete('sys_user', { id })
    await connection.execute(statement, [id])
  }

  // ================== 角色管理 ==================
  async selectRole(query) {
    const statement = SQLSTM.select('sys_role', {
      limit: {
        pageNum: query.pageNum,
        pageSize: query.pageSize,
      },
    })
    const [list] = await connection.execute(statement)
    const [[{ total }]] = await connection.execute(
      SQLSTM.select('sys_role', {
        attrs: [{ fun: 'count', attr: '*', as: 'total' }],
      })
    )
    return {
      list,
      total,
    }
  }
  async selectRoleAll() {
    const statement = `SELECT * FROM sys_role`
    const [result] = await connection.execute(statement)
    return result
  }
  async selectRoleRoutes(id) {
    const statement = `SELECT 
    JSON_ARRAYAGG(
      menuId
    ) menuIds FROM sys_role_menu WHERE roleId = ?;`
    const [result] = await connection.execute(statement, [id])
    return result[0].menuIds
  }
  async insertRole(body) {
    const id = snowflake.generate()
    const { roleName, status, roleSort, remark, routerList } = body
    const roleSTM = SQLSTM.insert('sys_role', { id, roleName, status, roleSort, remark })
    await connection.execute(roleSTM)
    const rolesMenuSTM = `INSERT INTO sys_role_menu (roleId, menuId) VALUES (?, ?)`
    for (const menuId of routerList) {
      await connection.execute(rolesMenuSTM, [id, menuId])
    }
  }
  async updateRole(body) {
    const { id, roleName, status, roleSort, remark, routerList } = body
    const roleSTM = SQLSTM.update('sys_role', {
      attrs: { roleName, status, roleSort, remark },
      where: { id },
    })
    await connection.execute(roleSTM)
    await connection.execute(`DELETE FROM sys_role_menu WHERE roleId = ?`, [id])
    const rolesMenuSTM = `INSERT INTO sys_role_menu (roleId, menuId) VALUES (?, ?)`
    for (const menuId of routerList) {
      await connection.execute(rolesMenuSTM, [id, menuId])
    }
  }
  async deleteRole(id) {
    const [result] = await connection.execute(`SELECT * FROM sys_user WHERE roleId = ?`, [id])
    if (result.length == 0) {
      await connection.execute(`DELETE FROM sys_role WHERE id = ?`, [id])
      await connection.execute(`DELETE FROM sys_role_menu WHERE roleId = ?`, [id])
      return true
    } else {
      return false
    }
  }

  // ================== 菜单管理 ==================
  async selectMenus() {
    const statement = SQLSTM.select('sys_menu', {
      order: [
        ['orderNum', 'ASC'],
        ['id', 'ASC'],
      ],
    })
    const result = await connection.execute(statement)
    return result[0]
  }
  async insertMenu(body) {
    const statement = SQLSTM.insert('sys_menu', body)
    await connection.execute(statement)
  }
  async updateMenu(body) {
    const id = body.id
    delete body.id
    const statement = SQLSTM.update('sys_menu', {
      attrs: body,
      where: { id },
    })
    const result = await connection.execute(statement)
    return result[0]
  }
  async deleteMenu(id) {
    const children = await connection.execute(SQLSTM.select('sys_menu', { where: { parentId: id } }))
    if (children[0].length) {
      return false
    } else {
      const statement = SQLSTM.delete('sys_menu', { id })
      await connection.execute(statement)
      return true
    }
  }

  // ================== 字典管理 ==================
  async selectDictType(query) {
    const statement = SQLSTM.select('sys_dict_type', {
      order: [['id', 'ASC']],
      limit: {
        pageNum: query.pageNum,
        pageSize: query.pageSize,
      },
    })
    const [list] = await connection.execute(statement)
    const [[{ total }]] = await connection.execute(
      SQLSTM.select('sys_dict_type', {
        attrs: [{ fun: 'count', attr: '*', as: 'total' }],
      })
    )
    return {
      list,
      total,
    }
  }
  async insertDictType(body) {
    const statement = SQLSTM.insert('sys_dict_type', body)
    await connection.execute(statement)
  }
  async updateDictType(body) {
    const id = body.id
    delete body.id
    const statement = SQLSTM.update('sys_dict_type', {
      attrs: body,
      where: { id },
    })
    await connection.execute(statement)
  }
  async delectDictType(id) {
    const [dictType] = await connection.execute('SELECT * FROM sys_dict_type WHERE id = ?', [id])
    await connection.execute(`DELETE FROM sys_dict_type WHERE id = ?`, [id])
    await connection.execute('DELETE FROM sys_dict_data WHERE dictType = ?', [dictType[0].dictType])
    return true
  }
  async selectDictData(params) {
    const statement = SQLSTM.select('sys_dict_data', {
      order: [['dictSort', 'ASC']],
    })
    const result = await connection.execute(statement)
    return result[0]
  }
  async insertDictData(body) {
    const statement = SQLSTM.insert('sys_dict_data', body)
    await connection.execute(statement)
  }
  async updateDictData(body) {
    const id = body.id
    delete body.id
    const statement = SQLSTM.update('sys_dict_data', {
      attrs: body,
      where: { id },
    })
    await connection.execute(statement)
  }
  async deleteDictData(id) {
    const statement = SQLSTM.delete('sys_dict_data', { id })
    await connection.execute(statement, [id])
  }
  // vue组件: 字典接口
  async selectTypeToData(params) {
    const statement = `SELECT * FROM sys_dict_data WHERE dictType = ? AND status = 0`
    const result = await connection.execute(statement, [params])
    return result[0]
  }

  // ================== 图片上传 ==================
  async createAvatar(filename, mimetype, size, id, path) {
    const statement = `INSERT INTO sys_avatar (filename, mimetype, size, userId, path) VALUES (?, ?, ?, ?, ?);`
    await connection.execute(statement, [filename, mimetype, size, id, path])
  }
  async updateAvatarUrlById(avatarUrl, id) {
    const statement = `UPDATE sys_user SET avatar = ? WHERE id = ?;`
    await connection.execute(statement, [avatarUrl, id])
  }
  async getAvatarByUserId(id) {
    console.log(id)
    const statement = `SELECT * FROM sys_avatar WHERE userId = ?;`
    const [result] = await connection.execute(statement, [id])
    return result.pop()
  }
  async createPicture(filename, mimetype, size, userId, path) {
    const statement = `INSERT INTO art_picture (filename, mimetype, size, userId, path) VALUES (?, ?, ?, ?, ?)`
    const [result] = await connection.execute(statement, [filename, mimetype, size, userId, path])
    return result
  }
  async getPictureByFilename(filename) {
    const statement = `SELECT * FROM art_picture WHERE filename = ?;`
    const [result] = await connection.execute(statement, [filename])
    return result[0]
  }
}

module.exports = new SystemService()
